const { pool } = require('../config/database');

class Stock {
  // 添加股票
  static async addStock(code, name) {
    try {
      const [result] = await pool.execute(
        'INSERT INTO stocks (code, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = ?',
        [code, name, name]
      );
      return result.insertId || (await this.getStockByCode(code)).id;
    } catch (error) {
      console.error('添加股票失败:', error);
      throw error;
    }
  }

  // 获取所有股票
  static async getAllStocks() {
    try {
      const [rows] = await pool.execute('SELECT * FROM stocks');
      return rows;
    } catch (error) {
      console.error('获取所有股票失败:', error);
      throw error;
    }
  }

  // 根据代码获取股票
  static async getStockByCode(code) {
    try {
      const [rows] = await pool.execute('SELECT * FROM stocks WHERE code = ?', [code]);
      return rows[0];
    } catch (error) {
      console.error('根据代码获取股票失败:', error);
      throw error;
    }
  }

  // 删除股票
  static async deleteStock(id) {
    try {
      const numericId = parseInt(id, 10);
      if (isNaN(numericId)) {
        throw new Error('Invalid parameter: id must be an integer');
      }
      
      // 先删除关联的价格记录
      await pool.execute('DELETE FROM stock_prices WHERE stock_id = ?', [numericId]);
      // 再删除股票记录
      const [result] = await pool.execute('DELETE FROM stocks WHERE id = ?', [numericId]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除股票失败:', error);
      throw error;
    }
  }

  // 添加股票价格
  static async addStockPrice(stockId, price, openPrice, closePrice, highPrice, lowPrice, yesterdayClosePrice = null, volume = null, timestamp = null) {
    try {
      const numericStockId = parseInt(stockId, 10);
      if (isNaN(numericStockId)) {
        throw new Error('Invalid parameter: stockId must be an integer');
      }
      
      // 确保所有价格参数都不是undefined，如果是则设为null
      const safePrice = price !== undefined ? price : null;
      const safeOpenPrice = openPrice !== undefined ? openPrice : null;
      const safeClosePrice = closePrice !== undefined ? closePrice : null;
      const safeHighPrice = highPrice !== undefined ? highPrice : null;
      const safeLowPrice = lowPrice !== undefined ? lowPrice : null;
      const safeYesterdayClosePrice = yesterdayClosePrice !== undefined ? yesterdayClosePrice : null;
      const safeVolume = volume !== undefined ? volume : null;
      
      // 如果提供了timestamp，则使用它；否则使用数据库默认值
      let query, params;
      if (timestamp) {
        // 将JavaScript时间转换为北京时间的MySQL格式
        const beijingTime = new Date(timestamp.getTime() + 8 * 60 * 60 * 1000);
        const mysqlTimestamp = beijingTime.toISOString().slice(0, 19).replace('T', ' ');
        query = 'INSERT INTO stock_prices (stock_id, price, open_price, close_price, high_price, low_price, yesterday_close_price, volume, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
        params = [numericStockId, safePrice, safeOpenPrice, safeClosePrice, safeHighPrice, safeLowPrice, safeYesterdayClosePrice, safeVolume, mysqlTimestamp];
      } else {
        query = 'INSERT INTO stock_prices (stock_id, price, open_price, close_price, high_price, low_price, yesterday_close_price, volume) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
        params = [numericStockId, safePrice, safeOpenPrice, safeClosePrice, safeHighPrice, safeLowPrice, safeYesterdayClosePrice, safeVolume];
      }
      console.log('query', query, 'params', params)

      const [result] = await pool.execute(query, params);
      return result.insertId;
    } catch (error) {
      console.error('添加股票价格失败:', error);
      throw error;
    }
  }

  // 获取股票的最新价格
  static async getLatestPrice(stockId) {
    try {
      const numericStockId = parseInt(stockId, 10);
      if (isNaN(numericStockId)) {
        throw new Error('Invalid parameter: stockId must be an integer');
      }
      const [rows] = await pool.execute(
        'SELECT * FROM stock_prices WHERE stock_id = ? ORDER BY timestamp DESC LIMIT 1',
        [numericStockId]
      );
      return rows[0];
    } catch (error) {
      console.error('获取最新价格失败:', error);
      throw error;
    }
  }

  // 获取股票的价格历史
  static async getPriceHistory(stockId, options = {}) {
    try {
      const numericStockId = parseInt(stockId, 10);
      const { limit = 100, offset = 0, startDate, endDate } = options;
      const numericLimit = parseInt(limit, 10);
      const numericOffset = parseInt(offset, 10);
      
      if (isNaN(numericStockId) || isNaN(numericLimit) || isNaN(numericOffset)) {
        throw new Error('Invalid parameters: stockId, limit and offset must be integers');
      }
      
      console.log('numericStockId', numericStockId, 'numericLimit', numericLimit, 'numericOffset', numericOffset)
      
      // 构建WHERE条件
      let whereConditions = [`stock_id = ${numericStockId}`];
      
      if (startDate) {
        whereConditions.push(`timestamp >= '${startDate}'`);
      }
      
      if (endDate) {
        whereConditions.push(`timestamp <= '${endDate} 23:59:59'`);
      }
      
      const whereClause = whereConditions.join(' AND ');
      
      // 获取总数
      const countSql = `SELECT COUNT(*) as total FROM stock_prices WHERE ${whereClause}`;
      const [countResult] = await pool.execute(countSql);
      const total = countResult[0].total;
      
      // 获取数据
      const sql = `SELECT * FROM stock_prices WHERE ${whereClause} ORDER BY timestamp DESC LIMIT ${numericLimit} OFFSET ${numericOffset}`;
      const [rows] = await pool.execute(sql);
      
      return {
        data: rows,
        total: total,
        limit: numericLimit,
        offset: numericOffset
      };
    } catch (error) {
      console.error('获取价格历史失败:', error);
      throw error;
    }
  }

  // 获取股票的当日价格数据
  static async getTodayPrices(stockId) {
    try {
      const numericStockId = parseInt(stockId, 10);
      if (isNaN(numericStockId)) {
        throw new Error('Invalid parameter: stockId must be an integer');
      }
      
      const today = new Date();
      today.setHours(0, 0, 0, 0);
      
      const [rows] = await pool.execute(
        'SELECT * FROM stock_prices WHERE stock_id = ? AND timestamp >= ? ORDER BY timestamp',
        [numericStockId, today]
      );
      return rows;
    } catch (error) {
      console.error('获取当日价格数据失败:', error);
      throw error;
    }
  }
}

module.exports = Stock;